#!/bin/bash

#创建ods层客户宽表 ods_tb_cust_info
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.ods_tb_cust_info (cif_no string,cif_name string,cif_type string,local_flag string,crt_date date,data_date date);"

#向客户宽表中更新数据 全量更新
hive -e "set hive.exec.mode.local.auto=true;truncate table daekeyijiance.ods_tb_cust_info;insert into daekeyijiance.ods_tb_cust_info (cif_no,cif_name,cif_type,local_flag,crt_date,data_date) select TRIM(cif_no),TRIM(cif_name),SUBSTR(cif_type,2,1),SUBSTR(local_flag,2,1),to_date(crt_date),to_date(data_date) from daekeyijiance.asv_tb_cust_info;"

#创建ods层交易宽表 ods_tb_cust_tx
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.ods_tb_cust_tx(trace_no string,cif_no string,cif_name string,cif_type string,opn_br_no string,tx_br_no string,ac_id string,ct_ind string,rece_pay_flag string,tx_amt double,tx_cny_amt double,tx_usd_amt double,tx_date date,tx_time timestamp,local_flag string,cb_flag string);"

#向交易宽表中更新数据 全量更新
hive -e "set hive.exec.mode.local.auto=true;truncate table daekeyijiance.ods_tb_cust_tx;insert into daekeyijiance.ods_tb_cust_tx (trace_no,cif_no,cif_name,cif_type,opn_br_no,tx_br_no,ac_id,ct_ind,rece_pay_flag,tx_amt,tx_cny_amt,tx_usd_amt,tx_date,tx_time,local_flag,cb_flag) select substr(trace_no,1,32),trim(cif_no),trim(cif_name),substr(cif_type,2,1),substr(opn_br_no,1,8),substr(tx_br_no,1,8),trim(ac_id),substr(ct_ind,2,1),substr(rece_pay_flag,2,1),cast(tx_amt as double),cast(tx_cny_amt as double),cast(tx_usd_amt as double),to_date(tx_date),from_unixtime(unix_timestamp(tx_time,'yyyy-MM-dd HH:mm:ss')),substr(local_flag,2,1),substr(cb_flag,2,1) from  daekeyijiance.asv_tb_cust_tx;"
